package jmine.tec.environment.db.server;

import java.io.File;
import java.net.URL;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import jmine.tec.persist.schema.api.SqlDialect;
import jmine.tec.persist.schema.impl.dialects.PostgreSQLDialect;
import bancosys.tec.utils.db.converter.DbConverter;
import bancosys.tec.utils.db.converter.OracleToPostgreSQLDbConverter;
import bancosys.tec.utils.db.executor.DbExecutor;
import bancosys.tec.utils.db.executor.PostgreSQLDbExecutor;

/**
 * @author Rafael Volpato
 * @date Dec 1, 2010
 */
public class PostgreSQLTestDBServer extends AbstractTestDBServer {

    /**
     * Limpa o banco de dados. <br>
     * Para esse banco o skipTables não está sendo utilizado.
     * 
     * @param skipTables skipTables
     */
    public void clearDBData(String... skipTables) {
        Connection conn = null;
        try {
            conn = this.getConnection();
            List<String> tables = this.getTableNames(conn);

            Statement st = conn.createStatement();

            /* Disable constraints check */
            st.executeUpdate("SET CONSTRAINTS ALL DEFERRED;");

            /* Delete table data */
            for (String table : tables) {
                st.executeUpdate("DELETE FROM " + table);
            }


        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                throw new TestDBServerException(e);
            }
        }
    }

    /**
     * {@inheritDoc}
     */
    public void dropTables() throws SQLException {
        Connection conn = null;
        try {
            conn = this.getConnection();
            List<String> tables = this.getTableNames(conn);

            Statement st = conn.createStatement();

            /* Disable constraints check */
            st.executeUpdate("SET CONSTRAINTS ALL DEFERRED;");

            /* Delete table data */
            for (String table : tables) {
                st.executeUpdate("DROP TABLE " + table + " CASCADE;");
            }

            for (String sequence : this.findSequences(conn, this.getDefaultSchema())) {
                st.executeUpdate("DROP SEQUENCE  " + sequence + ";");
            }
            st.execute("COMMIT;");
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                throw new TestDBServerException(e);
            }
        }
    }

    /**
     * @return O DbExecutor que deve ser utilizado oara este DbServer
     */
    public DbExecutor getDbExecutor() {
        return new PostgreSQLDbExecutor();
    }

    /**
     * @param inputFile O arquivo com o schema de criação do banco
     * @param outputFile O arquivo convertido
     * @param dbName O nome do banco
     * @return o DbConverter
     */
    public DbConverter getDbConverter(URL inputFile, File outputFile, String dbName) {
        return new OracleToPostgreSQLDbConverter(inputFile, outputFile, dbName);
    }

    /**
     * @return {@link PostgreSQLDialect}
     */
    public SqlDialect getDialect() {
        return new PostgreSQLDialect();
    }

    /**
     * {@inheritDoc}
     */
    public Set<String> findSequences(Connection connection, String schema) throws SQLException {
        Statement st = connection.createStatement();
        st.execute("BEGIN;");
        ResultSet rs =
                st.executeQuery(String.format("SELECT RELNAME FROM PG_STATIO_USER_SEQUENCES WHERE SCHEMANAME = '%s'", schema.toLowerCase()));
        Set<String> sequences = new HashSet<String>();
        while (rs.next()) {
            sequences.add(rs.getString("RELNAME").toUpperCase());
        }
        rs.close();
        st.close();
        return sequences;
    }
}
